package application.ClientCommunicationModule;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import application.CommunicationModule.Communication;
import application.client.data.Address;
import application.client.data.Client;


public class ClientDatabase {


	//String user;
	//String password;
	//Communication communication;

	Connection connection;

	Statement query;
	ResultSet result;

	Client client;
	static ArrayList<Client> clientList;	// zgodnie z �yczeniem modu�u komunikacji


	/*********************************************************************/
	/*************************** Constructors ***************************/
	/*******************************************************************/

	//	// ten konstruktor zostanie usunięty:
	//	public ClientDatabase(String user, String password) throws SQLException, ClassNotFoundException {
	//		super();
	//		ClientDatabase.clientList = new ArrayList<Client>();
	//		this.user = user; 
	//		this.password = password;
	//		
	//		communication = new Communication(this.user, this.password);
	//		this.connection = communication.getConnection();
	//		
	//		query = (Statement) connection.createStatement(); 
	//	}

	// trzeba wywoływać ten kostruktor:
	public ClientDatabase(Connection connection) throws SQLException, ClassNotFoundException {
		super();
		ClientDatabase.clientList = new ArrayList<Client>();
		this.connection = connection;
		query = (Statement) connection.createStatement(); 
	}

	// poniższy konstruktor już został zmieniony:
	public ClientDatabase(Connection connection, ArrayList<Client> list) throws SQLException, ClassNotFoundException {
		super();
		ClientDatabase.clientList = list;

		this.connection = connection;
		query = (Statement) connection.createStatement(); 
	}

	/*********************************************************************/
	/********************** Getters 'n' Setters *************************/
	/*******************************************************************/



	public String getUser() {
		return user;
	}

	public void setUser(String user) {
		this.user = user;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public Connection getConnection() {
		return connection;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	public Statement getQuery() {
		return query;
	}

	public void setQuery(Statement query) {
		this.query = query;
	}

	public ResultSet getResult() {
		return result;
	}

	public void setResult(ResultSet result) {
		this.result = result;
	}

	public Client getClient() {
		return client;
	}

	public void setClient(Client client) {
		this.client = client;
	}

	public static ArrayList<Client> getClientList() {
		return ClientDatabase.clientList;
	}

	public void setClientList(ArrayList<Client> clientList) {
		this.clientList = clientList;
	}

	/*********************************************************************/
	/**************************    Methods    ***************************/
	/*******************************************************************/


	int getCurrentID(String relation) throws SQLException{

		int tmpID = 0;

		this.result = ((java.sql.Statement) query).executeQuery( 
				" SELECT max(id) FROM \""+relation+"\"");

		if (result != null)  
			while(result.next())  
			{ 
				try {
					tmpID = this.result.getInt(1);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 
			}

		return (tmpID+1);
	}

	int getAddressID(int clientID) throws SQLException{
		int addressID = 0;

		this.result = ((java.sql.Statement) query).executeQuery( 
				" SELECT address FROM \"Clients\" WHERE id = "+clientID+""
				);

		if (result != null)  
			while(result.next())  
			{ 
				try {
					addressID = this.result.getInt(1);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 
			}
		return addressID;
	}

	public ArrayList<Client> getAllClients() throws SQLException{


		clientList.clear();

		this.result = ((java.sql.Statement) query).executeQuery(            
				"SELECT * FROM \"Clients\", \"Addresses\" WHERE \"Clients\".address = \"Addresses\".id");

		if (result != null)  
			while(result.next())  
			{ 
				try {

					client = new Client(Integer.parseInt(result.getString(1)),
							result.getString(2),
							result.getString(3),
							result.getString(4),
							result.getString(5),
							result.getString(6),
							new Address(result.getString(9),Integer.parseInt(result.getString(10)),Integer.parseInt(result.getString(11)),result.getString(12),result.getString(13)));

					clientList.add(client);

				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 

			}
		return clientList;
	}

	public boolean getClientsByField(String field, String value) throws SQLException{


		clientList.clear();

		if(field.equals("city") || field.equals("street") || field.equals("postCode") || field.equals("houseNumber") || field.equals("flatNumber")){
			this.result = ((java.sql.Statement) query).executeQuery(            
					"SELECT * FROM \"Clients\", \"Addresses\" WHERE \"Clients\".address = \"Addresses\".id AND \"Addresses\".\""+field+"\" = \'"+value+"\'"
					);
		}
		else{
			this.result = ((java.sql.Statement) query).executeQuery(            
					"SELECT * FROM \"Clients\", \"Addresses\" WHERE \"Clients\".address = \"Addresses\".id AND \"Clients\".\""+field+"\" = \'"+value+"\'"
					);
		}



		if (result != null)  
			while(result.next())  
			{ 
				try {

					client = new Client(Integer.parseInt(result.getString(1)),
							result.getString(2),
							result.getString(3),
							result.getString(4),
							result.getString(5),
							result.getString(6),
							new Address(result.getString(9),Integer.parseInt(result.getString(10)),Integer.parseInt(result.getString(11)),result.getString(12),result.getString(13)));
					clientList.add(client);

				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return false;
				} 

			}
		return true;
	}

	public boolean addClients() throws SQLException{

		try{

			for(Client k : clientList){

				int addressID = this.getCurrentID("Addresses");

				this.query.execute(
						"INSERT INTO \"Addresses\"(id, street, \"houseNumber\", \"flatNumber\", city, \"postCode\")" +
								" VALUES ("+addressID+",\'"+k.getAddress().getStreet()+"\', "+k.getAddress().getHouseNumber()+","+k.getAddress().getFlatNumber()+", \'"+k.getAddress().getCity()+"\', \'"+k.getAddress().getPostCode()+"\')"
						);	
				this.result = this.query.getResultSet();

				this.query.execute(
						"INSERT INTO \"Clients\"(id, company, LastName, \"firstName\", email, \"phoneNumber\", address)" +
								" VALUES ("+this.getCurrentID("Clients")+", \'"+k.getCompany()+"\',\'"+k.getLastName()+"\', \'"+k.getFirstName()+"\', \'"+k.getEmail()+"\',\'"+k.getPhoneNumber()+"\', "+addressID+")");

				this.result = this.query.getResultSet();
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} 

		return true;
	}

	public boolean addClient(Client client) throws SQLException{

		// 1) dla ka�dego go�cia wrzuconego do bufora client�w
		// 2) albo przekazanego od razu w wywo�aniu funkcji

		try {
			this.client = client;

			int addressID = this.getCurrentID("Addresses");


			this.query.execute(
					"INSERT INTO \"Addresses\"(id, street, \"houseNumber\", \"flatNumber\", city, \"postCode\")" +
							" VALUES ("+addressID+",\'"+client.getAddress().getStreet()+"\', "+client.getAddress().getHouseNumber()+","+client.getAddress().getFlatNumber()+", \'"+client.getAddress().getCity()+"\', \'"+client.getAddress().getPostCode()+"\')"
					);	
			this.result = this.query.getResultSet();


			this.query.execute(
					"INSERT INTO \"Clients\"(id, company, \"lastName\", \"firstName\", email, \"phoneNumber\", address)" +
							" VALUES ("+this.getCurrentID("Clients")+", \'"+client.getCompany()+"\',\'"+client.getLastName()+"\', \'"+client.getFirstName()+"\', \'"+client.getEmail()+"\',\'"+client.getPhoneNumber()+"\', "+addressID+")");

			this.result = this.query.getResultSet();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} 
		return true;
	}

	public boolean deleteClient(int id) throws SQLException{

		//this.client = k;
		try {

			this.query.execute(
					"DELETE FROM \"Clients\" WHERE id = "+id+""
					);	
			this.result = this.query.getResultSet();

			this.query.execute(
					"DELETE FROM \"Addresses\" WHERE id = "+this.getAddressID(id)+""
					);	

			this.result = this.query.getResultSet();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} 

		return true;
	}

	public boolean modifyClient(Client k) throws SQLException{

		try {
			String sql = "UPDATE \"Clients\" SET company=?, \"lastName\"=?, \"firstName\"=?, email=?, \"phoneNumber\"=? WHERE id = "+k.getClientID()+"";
			PreparedStatement prest = connection.prepareStatement(sql);
			prest.setString(1, k.getCompany());
			prest.setString(2, k.getLastName());
			prest.setString(3, k.getFirstName());
			prest.setString(4, k.getEmail());
			prest.setString(5, k.getPhoneNumber());

			prest.executeUpdate();

			sql = "UPDATE \"Addresses\"  SET street=?, \"houseNumber\"="+k.getAddress().getHouseNumber()+", \"flatNumber\"="+k.getAddress().getFlatNumber()+", city=?, \"postCode\"=? WHERE id = "+this.getAddressID(k.getClientID())+"";
			prest = connection.prepareStatement(sql);
			prest.setString(1, k.getAddress().getStreet());
			prest.setString(2, k.getAddress().getCity());
			prest.setString(3, k.getAddress().getPostCode());

			prest.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} 


		return true;
	}

	public void createTable() throws SQLException{

		this.query.execute(
				"CREATE TABLE \"Addresses\""+
						"("+
						" id integer NOT NULL,"+
						" street character varying(30) NOT NULL DEFAULT \'brak\'::character varying,"+
						" \"houseNumber\" integer NOT NULL,"+
						" \"flatNumber\" integer,"+
						" city character varying(20) NOT NULL DEFAULT \'brak\'::character varying,"+
						" \"postCode\" character varying(6) NOT NULL DEFAULT \'brak\'::character varying,"+
						" CONSTRAINT \"Addresses_pkey\" PRIMARY KEY (id)"+
						" CONSTRAINT post_code_check CHECK (\"postCode\"::text ~ '^([0-9]{2}-[0-9]{3})$'::text))"+
						"WITH (OIDS=FALSE);"
				);	
		this.result = this.query.getResultSet();

		this.query.execute(

				"CREATE TABLE \"Clients\""+
						"("+
						" id integer NOT NULL,"+
						" company character varying(20) NOT NULL DEFAULT \'brak\'::character varying,"+
						" \"lastName\" character varying(30) NOT NULL DEFAULT \'brak\'::character varying,"+
						" \"firstName\" character varying(20) NOT NULL DEFAULT \'brak\'::character varying,"+
						" email character varying(40) NOT NULL DEFAULT \'brak\'::character varying,"+
						" \"phoneNumber\" character varying(15) NOT NULL DEFAULT \'brak\'::character varying,"+
						" address integer NOT NULL,"+
						" CONSTRAINT \"Clients_pkey\" PRIMARY KEY (id),"+
						"CONSTRAINT \"Clients_Addresses_fkey\" FOREIGN KEY (address)"+
						" REFERENCES \"Addresses\" (id) MATCH FULL"+
						" ON UPDATE NO ACTION ON DELETE CASCADE,"+
						"CONSTRAINT id_check CHECK (id > 0),"+
						"CONSTRAINT last_name_check CHECK (\"lastName\"::text ~ '^([A-�]{1}[a-�]{1,}(-[A-�]{1}[a-�]{1,})?)$|^(brak)$'::text),"+
						"CONSTRAINT first_name_check CHECK (\"firstName\"::text ~ '^([A-�]{1}[a-�]{1,}( [A-�]{1}[a-�]{1,})?)$|^(brak)$'::text),"+
						"CONSTRAINT email_check CHECK (email::text ~ '^([_a-zA-Z0-9-]+(.[_a-zA-Z0-9-]+)*[@][a-zA-Z0-9-]+(.[a-zA-Z0-9-]{1,})*.([a-zA-Z]{2,}){1})$|^(brak)$'::text),"+
						"CONSTRAINT phone_number_check CHECK (\"phoneNumber\"::text ~ '^(([+]?([0-9]){1,3} )?[0-9]{3}(-([0-9]){3}){2})$|^(([+]([0-9]){1,3} )?[0-9]{2} [0-9]{3}(-([0-9]){2}){2})$|^(brak)$'::text)"+
						")"+
						"WITH (OIDS=FALSE);"
				);	
		this.result = this.query.getResultSet();


	}

	public void dropTable() throws SQLException{

		this.query.execute(
				"DROP TABLE \"Clients\""
				);	
		this.result = this.query.getResultSet();


		this.query.execute(
				"DROP TABLE \"Addresses\""
				);	
		this.result = this.query.getResultSet();

	}



}
